﻿using System;
using System.Data.OleDb;
using System.Data;
using System.IO;

public class ReadExcelFile
{

    public static String[] GetExcelSheetNames(string excelFileName)
    {
        OleDbConnection objConn = null;
        System.Data.DataTable dt = null;

        try
        {
            String connString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
                "Data Source=" + excelFileName + ";Extended Properties=Excel 8.0;";
            objConn = new OleDbConnection(connString);
            objConn.Open();
            dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
            if (dt == null)
            {
                return null;
            }
            String[] excelSheetNames = new String[dt.Rows.Count];
            int i = 0;

            foreach (DataRow row in dt.Rows)
            {
                excelSheetNames[i] = row["TABLE_NAME"].ToString();
                i++;
            }

            return excelSheetNames;
        }
        catch
        {
            return null;
        }
        finally
        {
            if (objConn != null)
            {
                objConn.Close();
                objConn.Dispose();
            }
            if (dt != null)
            {
                dt.Dispose();
            }
        }
    }

    public static DataSet GetExcelData(string excelFile, string sheetName)
    {
        DataSet ds = new DataSet();
        String connString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
                "Data Source=" + excelFile + ";Extended Properties=Excel 8.0;";
        string strSql = "Select * From [" + sheetName + "]";
        OleDbDataAdapter da = new OleDbDataAdapter(strSql, connString);
        try
        {
            da.Fill(ds);
            return ds;
        }
        catch (Exception ex)
        {
            strSql = ":\n" + ex.Message + "\n";
            return null;
        }

    }
    
    internal static String GetFilePath(string Name)
    {
        string retVal = "";

        //Vi du: C:\\IAS_BaoCao_120920100320.xls

        retVal = Path.GetTempPath() + "\\"+ Globals.assemblyTitle+"_"+ Name + "_" + DateTime.Now.ToString("yyMMddhhmmss") + ".xls";

        if (File.Exists(retVal))
        {
            File.Delete(retVal);
        }

        return retVal;
    }

    public static void Export(DataSet source, string Subject)
    {
        string fileName = Globals.convertToUnSign(Subject);
        fileName = GetFilePath(fileName);

        DataTable dt = source.Tables[0];
        System.IO.StreamWriter excelDoc;
        excelDoc = new System.IO.StreamWriter(fileName);
        const string startExcelXML = "<xml version>\r\n" +
              "<Workbook xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\"\r\n " +
              "xmlns:o=\"urn:schemas-microsoft-com:office:office\"\r\n " +
              "xmlns:x=\"urn:schemas-microsoft-com:office:excel\"\r\n " +
              "xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\">\r\n " +
              "<Styles>\r\n " +

              //Định dạng mặc định
              "<Style ss:ID=\"Default\" ss:Name=\"Normal\">\r\n " +
              "<Alignment ss:Vertical=\"Bottom\"/>\r\n " +
              "<Borders/>\r\n " +
              "<Font/>\r\n " +
              "<Interior/>\r\n " +
              "<NumberFormat/>\r\n " +
              "<Protection/>\r\n " +
              "</Style>\r\n " +
            //Định dạng cho tiêu đề cột
              "<Style ss:ID=\"BoldColumn\">\r\n " +
              "<Alignment ss:Horizontal=\"Center\" ss:Vertical=\"Bottom\"/>\r\n" +
              "<Font x:Family=\"Swiss\" ss:Bold=\"1\"/>\r\n " +
              "<Interior ss:Color=\"#FFFFFF\" ss:Pattern=\"Solid\"/>\r\n" +
              "<Borders>\r\n" +
              "<Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n" +
              "<Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n" +
              "<Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n" +
              "<Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n" +
              "</Borders>\r\n" +
              "</Style>\r\n " +

              //Định dạng chuỗi trong cột1
              "<Style ss:ID=\"StringLiteral1\">\r\n " +
              "<NumberFormat ss:Format=\"@\"/>\r\n " +
              "<Borders>\r\n" +
              "<Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n" +
              "<Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n" +
              "<Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n" +
              "<Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n" +
              "</Borders>\r\n" +
              "<Font ss:FontName=\"Arial\" x:Family=\"Swiss\" ss:Bold=\"1\"/>\r\n" +
              "</Style>\r\n " +


              //Định dạng chuỗi trong cột
              "<Style ss:ID=\"StringLiteral\">\r\n " +
              "<NumberFormat ss:Format=\"@\"/>\r\n " +
              "<Borders>\r\n" +
              "<Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n" +
              "<Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n" +
              "<Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n" +
              "<Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n" +
              "</Borders>\r\n" +
              "</Style>\r\n " +

              //Định dạng kiểu số thập phân 
              "<Style ss:ID=\"Decimal\">\r\n " +
              "<NumberFormat ss:Format=\"#,0\"/>\r\n " +
              "<Borders>\r\n" +
              "<Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n" +
              "<Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n" +
              "<Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n" +
              "<Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n" +
              "</Borders>\r\n" +
              "<Font ss:FontName=\"Arial\" x:Family=\"Swiss\" ss:Bold=\"1\"/>\r\n" +
              "</Style>\r\n " +

                //Định dạng kiểu số thập phân 1
              "<Style ss:ID=\"Decimal1\">\r\n " +
              "<NumberFormat ss:Format=\"#0.0\"/>\r\n " +
              "<Borders>\r\n" +
              "<Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n" +
              "<Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n" +
              "<Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n" +
              "<Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n" +
              "</Borders>\r\n" +

              "</Style>\r\n " +

                //Định dạng kiểu số thập phân 2
              "<Style ss:ID=\"Decimal2\">\r\n " +
              "<NumberFormat ss:Format=\"#,0.00\"/>\r\n " +
              "<Borders>\r\n" +
              "<Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n" +
              "<Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n" +
              "<Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n" +
              "<Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n" +
              "</Borders>\r\n" +

              "</Style>\r\n " +

              //Định dạng kiểu số nguyên
              "<Style ss:ID=\"Integer\">\r\n " +
              "<NumberFormat ss:Format=\"0\"/>\r\n " +
              "<Borders>\r\n" +
              "<Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n" +
              "<Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n" +
              "<Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n" +
              "<Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n" +
              "</Borders>\r\n" +
              "</Style>\r\n " +

              //Định dạng kiểu ngày tháng
              "<Style ss:ID=\"DateLiteral\">\r\n " +
              "<NumberFormat ss:Format=\"dd/MM/yyyy;@\"/>\r\n " +
              "<Borders>\r\n" +
              "<Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n" +
              "<Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n" +
              "<Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n" +
              "<Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n" +
              "</Borders>\r\n" +
              "</Style>\r\n " +

              //Định dạng tiêu đề
              "<Style ss:ID=\"Title\">\r\n " +
              "<Alignment ss:Horizontal=\"Left\" ss:Vertical=\"Bottom\"/>\r\n " +
              "<Font x:Family=\"Swiss\" ss:Size=\"14\" ss:Bold=\"1\"/>\r\n " +
              "</Style>\r\n " +

              //Định dạng tiêu đề1
              "<Style ss:ID=\"Title1\">\r\n " +
              "<Alignment ss:Horizontal=\"Left\" ss:Vertical=\"Bottom\"/>\r\n " +
              "<Font x:Family=\"Swiss\" ss:Size=\"11\" ss:Bold=\"1\"/>\r\n " +
              "</Style>\r\n " +

              "<Style ss:ID=\"Normal\">\r\n " +
              "<Alignment ss:Horizontal=\"Left\" ss:Vertical=\"Bottom\"/>\r\n " +
              "</Style>\r\n " +
              "</Styles>\r\n ";

        const string endExcelXML = "</Workbook>";

        int rowCount = 0;
        int sheetCount = 1;
        int colCount = dt.Columns.Count;
        excelDoc.Write(startExcelXML);
        excelDoc.Write("<Worksheet ss:Name=\"Sheet" + sheetCount + "\">\r\n ");
        excelDoc.Write("<Table x:FullColumns=\"1\" x:FullRows=\"1\" >");

        for (int x = 0; x < colCount; x++)
        {
            excelDoc.Write("<Column  ss:Width=\"80\" ss:AutoFitWidth=\"1\" />\r\n ");
        }
        string MergeAcross = (colCount - 1).ToString();
        // Tiêu đề
        excelDoc.Write("<Row ss:Height=\"18\">\r\n ");
        excelDoc.Write("<Cell ss:Index=\"1\" ss:MergeAcross=\"" + MergeAcross + "\" ss:StyleID=\"Title\"><Data ss:Type=\"String\">" + Subject + "</Data></Cell> \r\n");
        excelDoc.Write("</Row>\r\n ");
        // Ngày xuất
        excelDoc.Write("<Row>\r\n ");
        excelDoc.Write("<Cell ss:Index=\"1\" ss:MergeAcross=\"" + MergeAcross + "\" ss:StyleID=\"Title1\"><Data ss:Type=\"String\">Xuất ngày: " + DateTime.Now.ToString("dd/MM/yyyy hh:mm tt") + "</Data></Cell> \r\n");
        excelDoc.Write("</Row>\r\n ");
        // 1 dòng trắng
        excelDoc.Write("<Row>\r\n ");
        excelDoc.Write("<Cell ss:Index=\"1\" ss:MergeAcross=\"" + MergeAcross + "\" ss:StyleID=\"Normal\"><Data ss:Type=\"String\"></Data></Cell> \r\n");
        excelDoc.Write("</Row>\r\n ");
        // Tổng số dòng
        excelDoc.Write("<Row ss:Height=\"16\">\r\n ");
        excelDoc.Write("<Cell ss:Index=\"1\" ss:MergeAcross=\"" + MergeAcross + "\" ss:StyleID=\"Title1\"><Data ss:Type=\"String\">Tổng số dòng: " + dt.Rows.Count.ToString() + "</Data></Cell> \r\n");
        excelDoc.Write("</Row>\r\n ");

        excelDoc.Write("<Row>\r\n ");

        for (int x = 0; x < colCount; x++)
        {
            excelDoc.Write("<Cell ss:StyleID=\"BoldColumn\"><Data ss:Type=\"String\">");
            excelDoc.Write(dt.Columns[x].Caption);
            excelDoc.Write("</Data></Cell>\r\n ");
        }

        excelDoc.Write("</Row>\r\n ");
        foreach (DataRow x in dt.Rows)
        {
            rowCount++;
            //if the number of rows is > 64000 create a new page to continue output

            if (rowCount == 64000)
            {
                rowCount = 0;
                sheetCount++;
                excelDoc.Write("</Table>");
                excelDoc.Write(" </Worksheet>");
                excelDoc.Write("<Worksheet ss:Name=\"Sheet" + sheetCount + "\">");
                excelDoc.Write("<Table>");
            }
            excelDoc.Write("<Row>"); //ID=" + rowCount + "

            for (int y = 0; y < colCount; y++)
            {
                System.Type rowType;
                rowType = x[y].GetType();
                switch (rowType.ToString())
                {
                    case "System.String":
                        string XMLstring = x[y].ToString();
                        XMLstring = XMLstring.Trim();
                        XMLstring = XMLstring.Replace("&", "&amp;");
                        XMLstring = XMLstring.Replace(">", "&gt;");
                        XMLstring = XMLstring.Replace("<", "&lt;");
                        XMLstring = XMLstring.Replace("'", "&apos;");
                        XMLstring = XMLstring.Replace("\"", "&quot;");

                        //Xét định dạng chuỗi tùy theo cột
                        excelDoc.Write("<Cell ss:StyleID=\"StringLiteral\">" +
                                       "<Data ss:Type=\"String\">");

                        excelDoc.Write(XMLstring);
                        excelDoc.Write("</Data></Cell>");
                        break;
                    case "System.DateTime":

                        //Excel has a specific Date Format of YYYY-MM-DD followed by  
                        //the letter 'T' then hh:mm:sss.lll Example 2005-01-31T24:01:21.000
                        //The Following Code puts the date stored in XMLDate 
                        //to the format above

                        DateTime XMLDate = (DateTime)x[y];
                        string XMLDatetoString = ""; //Excel Converted Date

                        XMLDatetoString = XMLDate.Year.ToString() +
                             "-" +
                             (XMLDate.Month < 10 ? "0" +
                             XMLDate.Month.ToString() : XMLDate.Month.ToString()) +
                             "-" +
                             (XMLDate.Day < 10 ? "0" +
                             XMLDate.Day.ToString() : XMLDate.Day.ToString()) +
                             "T" +
                             (XMLDate.Hour < 10 ? "0" +
                             XMLDate.Hour.ToString() : XMLDate.Hour.ToString()) +
                             ":" +
                             (XMLDate.Minute < 10 ? "0" +
                             XMLDate.Minute.ToString() : XMLDate.Minute.ToString()) +
                             ":" +
                             (XMLDate.Second < 10 ? "0" +
                             XMLDate.Second.ToString() : XMLDate.Second.ToString()) +
                             ".000";
                        excelDoc.Write("<Cell ss:StyleID=\"DateLiteral\">" +
                                     "<Data ss:Type=\"DateTime\">");
                        excelDoc.Write(XMLDatetoString);
                        excelDoc.Write("</Data></Cell>");
                        break;
                    case "System.Boolean":
                        excelDoc.Write("<Cell ss:StyleID=\"StringLiteral\">" +
                                    "<Data ss:Type=\"String\">");
                        excelDoc.Write(x[y].ToString());
                        excelDoc.Write("</Data></Cell>");
                        break;
                    case "System.Int16":
                    case "System.Int32":
                    case "System.Int64":
                    case "System.Byte":
                        excelDoc.Write("<Cell ss:StyleID=\"Integer\">" +
                                "<Data ss:Type=\"Number\">");
                        excelDoc.Write(x[y].ToString());
                        excelDoc.Write("</Data></Cell>");
                        break;
                    case "System.Decimal":
                    case "System.Double":
                        if (dt.Columns[y].Caption.Contains("VND"))
                            excelDoc.Write("<Cell ss:StyleID=\"Decimal\">" +
                                  "<Data ss:Type=\"Number\">");
                        else
                            excelDoc.Write("<Cell ss:StyleID=\"Decimal2\">" +
                              "<Data ss:Type=\"Number\">");

                        excelDoc.Write(x[y].ToString().Replace(Globals.CS_DECIMAL_SYMBOL, "."));
                        excelDoc.Write("</Data></Cell>");
                        break;
                    case "System.DBNull":
                        excelDoc.Write("<Cell ss:StyleID=\"StringLiteral\">" +
                              "<Data ss:Type=\"String\">");
                        excelDoc.Write("");
                        excelDoc.Write("</Data></Cell>");
                        break;
                    default:
                        throw (new Exception(rowType.ToString() + " Not Handled."));
                }
            }
            excelDoc.Write("</Row>");
        }
        excelDoc.Write("</Table>");
        excelDoc.Write(" </Worksheet>");
        excelDoc.Write(endExcelXML);
        excelDoc.Close();

        try
        {
            System.Diagnostics.Process.Start(fileName);
        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message);
        }
    }

    public static void Export(DataTable source, string Subject)
    {
        DataSet ds = new DataSet();
        ds.Tables.Add(source.Copy());
        Export(ds, Subject);
    }

    public static void MSExcelExport(DataSet source, string Subject, params string[] parameterHeaders)
    {
        string fileName = Globals.convertToUnSign(Subject);
        try
        {
            MSExcelUtil excel = new MSExcelUtil(fileName, Subject);
            string filePath = excel.Export(source, parameterHeaders);

            System.Diagnostics.Process.Start(filePath);
        }
        catch (Exception)
        {
            throw new Exception("Không mở được File \n\n" + fileName);
        }
    }

    public static void MSExcelExport(DataTable source, string Subject, params string[] parameterHeaders)
    {
        //DataSet ds = new DataSet();
        //ds.Tables.Add(source.Copy());
        //MSExcelExport(ds, Subject, parameterHeaders);

        string fileName = Globals.convertToUnSign(Subject);
        try
        {
            MSExcelUtil excel = new MSExcelUtil(fileName, Subject);
            string filePath = excel.Export(source, Subject, parameterHeaders);

            System.Diagnostics.Process.Start(filePath);
        }
        catch (Exception)
        {
            throw new Exception("Không mở được File \n\n" + fileName);
        }
    }

    public static void MSExcelExport(DataTable source, string Subject, string companyName, string chiNhanh, string strPeriod)
    {
        string _chiNhanh = "Chi nhánh: " + chiNhanh;
        
        MSExcelExport(source, Subject, companyName.ToUpper(), "", Subject.ToUpper(), "", _chiNhanh, strPeriod, "");
    }
}
